package com.xzk.ExpressInnWeb.dao.daoImp;

import com.xzk.ExpressInnWeb.bean.Express;
import com.xzk.ExpressInnWeb.dao.BaseExpressDao;
import com.xzk.ExpressInnWeb.exception.DuplicateCodeException;
import com.xzk.ExpressInnWeb.util.DruidUtil;
import com.xzk.ExpressInnWeb.util.ParamType;
import com.xzk.ExpressInnWeb.util.QueryUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;

public class ExpressDaoMySQL implements BaseExpressDao {

    private static final String SQL_FIND_EX_BY_ID = "SELECT * FROM EXPRESS WHERE ID=?";
    /**
     * 查询4个计数值：快递总数、今日新增快递数(无论是否已经取件)、待取件快递总数、今日新增未取件快递数
     */
    private static final String SQL_CONSOLE_EX
            = "SELECT COUNT(ID) AS DATA1_SIZE, COUNT(TO_DAYS(INTIME)=TO_DAYS(NOW()) OR NULL) AS DATA1_DAY, COUNT(STATUS=0 OR NULL) AS DATA2_SIZE, COUNT(TO_DAYS(INTIME)=TO_DAYS(NOW()) AND STATUS=0 OR NULL) AS DATA2_DAY FROM EXPRESS";
    /**
     * 查询数据库中的所有快递信息
     */
    private static final String SQL_FIND_ALL_EX
            = "SELECT * FROM EXPRESS";
    /**
     * 分页查询数据库中的所有快递信息
     */
    private static final String SQL_FIND_ALL_EX_WITH_LIMIT
            = "SELECT * FROM EXPRESS LIMIT ? OFFSET ?";
    /**
     * 通过单号查询快递信息
     */
    private static final String SQL_FIND_EX_BY_EX_NUM
            = "SELECT * FROM EXPRESS WHERE EX_NUM=?";
    /**
     * 通过取件码查询快递信息
     */
    private static final String SQL_FIND_EX_BY_CODE
            = "SELECT * FROM EXPRESS WHERE CODE=?";
    /**
     * 通过收件人手机号查询快递信息
     */
    private static final String SQL_FIND_EX_BY_USER_PHONE
            = "SELECT * FROM EXPRESS WHERE USERPHONE=?";
    /**
     * 通过快递录入人的手机号查询快递信息
     */
    private static final String SQL_FIND_EX_BY_STAFF_PHONE
            = "SELECT * FROM EXPRESS WHERE STAFFPHONE=?";
    /**
     * 录入快递
     */
    private static final String SQL_INSERT_EX
            = "INSERT INTO EXPRESS (EX_NUM,USERNAME,USERPHONE,COMPANY,CODE,INTIME,STATUS,STAFFPHONE) VALUES(?,?,?,?,?,NOW(),0,?)";
    /**
     * 根据id修改快递信息更改
     * 注意：如果修改手机号，则需要更改取件码(生成新的取件码)，
     * 故修改手机号需要采用删除后新增快递的方式进行信息更改
     * 不在此进行手机号的修改
     */
    private static final String SQL_UPDATE_EX
            = "UPDATE EXPRESS SET EX_NUM=?,USERNAME=?,COMPANY=?,STATUS=? WHERE ID=?";
    /**
     * 根据取件码改变状态码(取件)
     * 问题：出库时间和出库件取件码清空？
     */
    private static final String SQL_UPDATE_EX_STATUS
            = "UPDATE EXPRESS SET STATUS=?,OUTTIME=NOW(),CODE=? WHERE CODE=?";
    /**
     * 根据id删除快递
     */
    private static final String SQL_DELETE_EX
            = "DELETE FROM EXPRESS WHERE ID=?";

    /**
     * 懒人排行 (总-年-月)，对express表的用户以及快递数进行排序
     * 查询获得的值为 用户名，用户手机号 和 快递数量 字段的 ResultSet，并根据快递数量降序排序
     */
    private static final String SQL_LAZY_BOARD = "SELECT USERNAME,COUNT(USERNAME) AS NUM FROM EXPRESS GROUP BY USERNAME ORDER BY NUM DESC LIMIT 3";
    private static final String SQL_LAZY_BOARD_YEAR = "SELECT USERNAME,COUNT(USERNAME) AS NUM FROM EXPRESS WHERE YEAR(NOW())=YEAR(INTIME) GROUP BY USERNAME ORDER BY NUM DESC LIMIT 3";
    private static final String SQL_LAZY_BOARD_MONTH = "SELECT USERNAME,COUNT(USERNAME) AS NUM FROM EXPRESS WHERE MONTH(NOW())=MONTH(INTIME) GROUP BY USERNAME ORDER BY NUM DESC LIMIT 3";

    private Connection conn = null;
    private PreparedStatement state = null;
    private ResultSet rs = null;

    @Override
    public List<Map<String, Integer>> consoleEx() {
        ArrayList<Map<String, Integer>> data = new ArrayList<>();
        // 1. 获取数据库连接
        conn = DruidUtil.getConnection();
        try {
            // 2. 预编译SQL语句
            state = conn.prepareStatement(SQL_CONSOLE_EX);
            // 3. 填充参数 (可选)
            // 4. 执行SQL语句
            rs = state.executeQuery();
            // 5. 获取执行结果
            if (rs.next()) {
                int data1_size = rs.getInt("data1_size");
                int data1_day = rs.getInt("data1_day");
                int data2_size = rs.getInt("data2_size");
                int data2_day = rs.getInt("data2_day");
                Map<String,Integer> data1 = new HashMap<>();
                data1.put("data1_size",data1_size);
                data1.put("data1_day",data1_day);
                Map<String,Integer> data2 = new HashMap<>();
                data2.put("data2_size",data2_size);
                data2.put("data2_day",data2_day);
                data.add(data1);
                data.add(data2);
            }
        } catch (SQLException|NullPointerException throwables) {
            throwables.printStackTrace();
        } finally {
            // 6. 资源释放
            DruidUtil.close(conn, state, rs);
        }
        return data;
    }

    @Override
    public List<Express> findAllEx(boolean limit, int offset, int exSize) {
        ArrayList<Express> data = new ArrayList<>();
        if (limit) {
            rs = QueryUtil.query(
                    SQL_FIND_ALL_EX_WITH_LIMIT,
                    QueryUtil.getList(QueryUtil.getMap(ParamType.INT,exSize),
                            QueryUtil.getMap(ParamType.INT,offset)), state, conn);
        } else {
            rs = QueryUtil.query(SQL_FIND_ALL_EX, null, state, conn);
        }
        try {
            while (rs.next()) {
                data.add(QueryUtil.buildExpress(rs));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidUtil.close(conn, state, rs);
        }
        return data;
    }

    private Express getExpress(String sql, String param) {
        Express ex = null;
        rs = QueryUtil.query(
                sql,
                QueryUtil.getList(QueryUtil.getMap(ParamType.STRING,param)), state, conn);
        try {
            if (rs.next()) {
                ex = QueryUtil.buildExpress(rs);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidUtil.close(conn, state, rs);
        }
        return ex;
    }

    private List<Express> getExpressList(String sql, String param) {
        ArrayList<Express> list = new ArrayList<>();
        rs = QueryUtil.query(
                sql,
                QueryUtil.getList(QueryUtil.getMap(ParamType.STRING,param)),
                state,conn);
        try {
            while (rs.next()) {
                list.add(QueryUtil.buildExpress(rs));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidUtil.close(conn, state, rs);
        }
        return list;
    }

    private List<Map> getLazyBoardValues(String sql){
        List<Map> list = new ArrayList<>();
        rs = QueryUtil.query(sql,null,state,conn);
        try {
            while (rs.next()) {
                String name = rs.getString("username");
                int num = rs.getInt("num");
                Map map  = new HashMap();
                map.put("username", name);
                map.put("num", num);
                list.add(map);
            }
        } catch (SQLException|NullPointerException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidUtil.close(conn,state,rs);
        }
        return list;
    }

    @Override
    public List<Map> getLazyBoardValues(int code)  {
        switch (code){
            case 0:
                return getLazyBoardValues(SQL_LAZY_BOARD);
            case 1:
                return getLazyBoardValues(SQL_LAZY_BOARD_YEAR);
            case 2:
                return getLazyBoardValues(SQL_LAZY_BOARD_MONTH);
            default:
                return null;
        }
    }

    @Override
    public Express findExByExNum(String ex_num) {
        return getExpress(SQL_FIND_EX_BY_EX_NUM, ex_num);
    }

    @Override
    public Express findExByCode(String code) {
        return getExpress(SQL_FIND_EX_BY_CODE, code);
    }

    @Override
    public List<Express> findAllExByUserPhone(String userPhone) {
        return getExpressList(SQL_FIND_EX_BY_USER_PHONE, userPhone);
    }

    @Override
    public List<Express> findAllExByStaffPhone(String staffPhone) {
        return getExpressList(SQL_FIND_EX_BY_STAFF_PHONE, staffPhone);
    }

    @Override
    public boolean insertEx(Express ex) throws DuplicateCodeException {
        // 快递单号重复：反馈给前端提示操作失败(单号已用)
        String ex_num = ex.getEx_num();
        String username = ex.getUsername();
        String userphone = ex.getUserphone();
        String company = ex.getCompany();
        // 取件码重复，则生成新的取件码，因取件码是由系统自动生成
        // 需要自定义异常类，上层方法捕获该异常类的对象在catch块中处理
        String code = ex.getCode();
        String staffphone = ex.getStaffphone();
        boolean result = false;
        try {
            result = QueryUtil.queryDML(
                    SQL_INSERT_EX,
                    QueryUtil.getList(
                            QueryUtil.getMap(ParamType.STRING,ex_num),
                            QueryUtil.getMap(ParamType.STRING,username),
                            QueryUtil.getMap(ParamType.STRING,userphone),
                            QueryUtil.getMap(ParamType.STRING,company),
                            QueryUtil.getMap(ParamType.STRING,code),
                            QueryUtil.getMap(ParamType.STRING,staffphone)), state, conn);
        } catch (SQLException throwables) {
            if (throwables.getMessage().endsWith("for key 'code'")) {
                System.err.println("产生异常：" + throwables.getMessage() + "并抛出");
                throw new DuplicateCodeException(throwables.getMessage());
            } else if (throwables.getMessage().endsWith("for key 'ex_num'")) {
                System.err.println("产生异常：" + throwables.getMessage());
            } else {
                throwables.printStackTrace();
            }
        }
        return result;
    }

    @Override
    public boolean updateEx(int id, Express newEx) {
        // 问题：ex_num查重？若ex_num重复，则操作失败，直接返回前端
        // 手机号与用户姓名的关联？在快递表无法进行关联的管理，试想多个不同姓名收件人的手机号也可以一样
        // 单号变了取件码却不变？取件码若与手机号强相关，只要能保证不会重复，无需改变
        String ex_num = newEx.getEx_num();
        String username = newEx.getUsername();
        String company = newEx.getCompany();
        // 若要修改快递状态为已取件(1)，则需要另外调用updateExStatus方法
        int statusOld = findExById(id).getStatus();
        int status = newEx.getStatus();
        boolean statusUnchanged = status == statusOld;
        boolean result = false;
        try {
            result = QueryUtil.queryDML(
                    SQL_UPDATE_EX,
                    QueryUtil.getList(
                            QueryUtil.getMap(ParamType.STRING,ex_num),
                            QueryUtil.getMap(ParamType.STRING,username),
                            QueryUtil.getMap(ParamType.STRING,company),
                            QueryUtil.getMap(ParamType.INT,status),
                            QueryUtil.getMap(ParamType.INT,id)), state, conn);
            // 如果更改成功，则调用updateExStatus方法对出库时间和取件码进行修改
            if (result && status == 1 && !statusUnchanged) {
                updateExStatus(findExByExNum(ex_num).getCode());
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return result;
    }

    @Override
    public boolean updateExStatus(String code) {
        boolean result = false;
        try {
            result = QueryUtil.queryDML(
                    SQL_UPDATE_EX_STATUS,
                    QueryUtil.getList(
                            QueryUtil.getMap(ParamType.INT,1),
                            QueryUtil.getMap(ParamType.NULL,null),
                            QueryUtil.getMap(ParamType.STRING,code)), state, conn);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return result;
    }

    @Override
    public boolean deleteEx(int id) {
        boolean result = false;
        try {
            result = QueryUtil.queryDML(
                    SQL_DELETE_EX,
                    QueryUtil.getList(
                            QueryUtil.getMap(ParamType.INT,id)), state, conn);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return result;
    }

    private Express findExById(int id) {
        Express ex = null;
        rs = QueryUtil.query(
                SQL_FIND_EX_BY_ID,
                QueryUtil.getList(QueryUtil.getMap(ParamType.INT,id)), state, conn);
        try {
            if (rs.next()) {
                ex = QueryUtil.buildExpress(rs);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidUtil.close(conn, state, rs);
        }
        return ex;
    }

}
